package cn.taobaoke.restful.domain;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

/**
 * @author yangzuo
 * @since 2017-03-11
 */
public interface AgentRepository extends JpaRepository<AgentEntity, Long> {

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and agent.domainId = :domainId")
    List<AgentEntity> findAll(@Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.id = :id and agent.domainId = :domainId")
    Optional<AgentEntity> findByIdAndDomainId(@Param("id") Long agentId, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.id in :agentIds and agent.domainId = :domainId")
    List<AgentEntity> findByIdIn(@Param("agentIds") Collection<Long> agentIds, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.parentId = null and agent.domainId = :domainId")
    List<AgentEntity> findByParentIdNull(@Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.parentId = :agentId and agent.domainId = :domainId")
    List<AgentEntity> findByParentId(@Param("agentId") Long agentId, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.weChat = :weChat and agent.domainId = :domainId")
    Optional<AgentEntity> findByWeChat(@Param("weChat") String weChat, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.name = :name and agent.domainId = :domainId")
    Optional<AgentEntity> findByName(@Param("name") String name, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and ( agent.weChat = :keyword or agent.name = :keyword) and agent.password = :password and agent.domainId = :domainId")
    Optional<AgentEntity> findByKeywordAndPassword(@Param("keyword") String keyword, @Param("password") String password, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.pid = :pid and agent.domainId = :domainId")
    Optional<AgentEntity> findByPid(@Param("pid") String pid, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.token = :token and agent.domainId = :domainId")
    Optional<AgentEntity> findByToken(@Param("token") String token, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and ( agent.name like :keyword or agent.phone like :keyword or agent.pid like :keyword or agent.weChat like :keyword ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeywordPage(@Param("keyword") String keyword, @Param("domainId") Long domainId, Pageable pageable);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and (agent.name like :keyword or agent.phone like :keyword or agent.pid like :keyword or agent.weChat like :keyword ) and agent.domainId = :domainId")
    List<AgentEntity> findByKeywords(@Param("keyword") String keyword, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and (agent.weChat like %:keyword% or agent.name like %:keyword% or agent.phone like %:keyword% ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeyword(@Param("keyword") String keyword, @Param("domainId") Long domainId, Pageable pageable);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and agent.type = :classType and (agent.weChat like %:keyword% or agent.name like %:keyword% or agent.phone like %:keyword% ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeywordAndType(@Param("keyword") String keyword, @Param("classType") String type, @Param("domainId") Long domainId, Pageable pageable);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and agent.parentId = :parentId and (agent.weChat like %:keyword% or agent.name like %:keyword% or agent.phone like %:keyword% ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeywordAndParentId(@Param("parentId") Long parentId, @Param("keyword") String keyword, @Param("domainId") Long domainId, Pageable pageable);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and agent.parentId = :parentId and (agent.weChat like %:keyword% or agent.name like %:keyword% or agent.phone like %:keyword% ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeywordAndParentId(@Param("keyword") String keyword, @Param("parentId") Long parentId, @Param("domainId") Long domainId, Pageable pageable);

    @Query(value = "select a1.* from tb_agent a1 left join tb_agent a2 on a1.parent_id = a2.id where (a1.is_delete = null or a1.is_delete != true) and a1.parent_id = :id and a1.domain_id = :domainId", nativeQuery = true)
        // @Query(value = "select a1.* from AgentEntity a1 left join AgentEntity a2 on a1.parentId = a2.id where (a1.isDelete = null or a1.isDelete != true) and a1.parentId = :id ", nativeQuery = true)
    List<AgentEntity> findClass2Agents(@Param("id") Long agentId, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true ) and agent.type = :classType and agent.domainId = :domainId")
    List<AgentEntity> findByType(@Param("classType") String type, @Param("domainId") Long domainId);

    @Query("from AgentEntity agent where (agent.isDelete = null or agent.isDelete != true) and (agent.parentId = :ancestorId or agent.grandParentId = :ancestorId) and (agent.weChat like %:keyword% or agent.name like %:keyword% or agent.phone like %:keyword% ) and agent.domainId = :domainId")
    Page<AgentEntity> findByKeywordAndParentIdOrGrandParentId(@Param("ancestorId") Long ancestorId, @Param("keyword") String keyword, @Param("domainId") Long domainId, Pageable pageable);

    @Query("update AgentEntity agent set agent.isDelete = true where (agent.id = :agentId or agent.parentId = :agentId or agent.grandParentId = :agentId) and agent.domainId = :domainId")
    @Modifying
    @Transactional
    void deleteByIdOrParentIdOrGrandParentId(@Param("agentId") Long agentId, @Param("domainId") Long domainId);
}
